今天來介紹關連式資料庫查詢方便的地方-JOIN
用以下3個表的紀錄內容來解釋,使用不同JOIN方法在資料表之中的資料顯示差異~
mysql> select * from act;
+------------+--------+------------+------------+----------------+------------+------------+
| actives_id | status | start_time | end_time | platform_id | updated_at | created_at |
+------------+--------+------------+------------+----------------+------------+------------+
| 1 | 1 | 1611504000 | 1632931200 | 10868213102191 | 1632499200 | 1632499200 |
+------------+--------+------------+------------+----------------+------------+------------+
1 row in set (0.00 sec)
mysql> select * from act_game;
+------------+-----------------+-------------------------------+------+------------+
| actives_id | game_name | support_rules | type | created_at |
+------------+-----------------+-------------------------------+------+------------+
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | 7 | 1632499200 |
| 2 | INVEST-ITHOME99 | ["z920"] | 5 | 1632499200 |
+------------+-----------------+-------------------------------+------+------------+
2 rows in set (0.00 sec)
mysql> select * from act_name;
+------------+--------------+----------+------------+
| actives_id | actives_name | language | created_at |
+------------+--------------+----------+------------+
| 1 | test2 | ch | 1632499205 |
| 1 | test1 | en | 1632499200 |
| 1 | test3 | jp | 1632499209 |
| 3 | test01 | ch | 1632499220 |
| 3 | test02 | en | 1632499225 |
| 3 | test03 | jp | 1632499230 |
+------------+--------------+----------+------------+
6 rows in set (0.00 sec)
使用: 連結act_game&act_name取得遊戲內容包含使用語言資訊。
語法:
SELECT table_column1, table_column2...
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
mysql> SELECT act_game.actives_id , act_game.game_name , act_game.support_rules , act_name.language
FROM act_game
INNER JOIN act_name
ON act_game.actives_id=act_name.actives_id;
+------------+-----------------+-------------------------------+----------+
| actives_id | game_name | support_rules | language |
+------------+-----------------+-------------------------------+----------+
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | ch |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | en |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | jp |
+------------+-----------------+-------------------------------+----------+
3 rows in set (0.00 sec)
使用: 連結act_game&act_name取得遊戲內容包含使用語言資訊。
語法:
SELECT column1, column2...
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
mysql> SELECT act_game.actives_id , act_game.game_name , act_game.support_rules , act_name.language
FROM act_game
LEFT JOIN act_name
ON act_game.actives_id=act_name.actives_id;
+------------+-----------------+-------------------------------+----------+
| actives_id | game_name | support_rules | language |
+------------+-----------------+-------------------------------+----------+
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | ch |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | en |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | jp |
| 2 | INVEST-ITHOME99 | ["z920"] | NULL |
+------------+-----------------+-------------------------------+----------+
4 rows in set (0.00 sec)
使用: 連結act_game&act_name取得遊戲內容包含使用語言資訊。
語法:
SELECT column1, column2...
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
mysql> SELECT act_game.actives_id , act_game.game_name , act_game.support_rules , act_name.language
FROM act_game
RIGHT JOIN act_name
ON act_game.actives_id=act_name.actives_id;
+------------+-----------------+-------------------------------+----------+
| actives_id | game_name | support_rules | language |
+------------+-----------------+-------------------------------+----------+
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | ch |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | en |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | jp |
| NULL | NULL | NULL | ch |
| NULL | NULL | NULL | en |
| NULL | NULL | NULL | jp |
+------------+-----------------+-------------------------------+----------+
6 rows in set (0.00 sec)
使用: 連結act_game&act_name取得遊戲內容包含使用語言資訊。
範例中: act_game (2筆記錄) * act_name (6筆記錄) = 返回12筆結果。
語法:
SELECT table_column1, table_column2...
FROM table_name1
CROSS JOIN table_name2;
mysql> SELECT act_game.actives_id , act_game.game_name , act_game.support_rules , act_name.language
FROM act_game
CROSS JOIN act_name;
+------------+-----------------+-------------------------------+----------+
| actives_id | game_name | support_rules | language |
+------------+-----------------+-------------------------------+----------+
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | ch |
| 2 | INVEST-ITHOME99 | ["z920"] | ch |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | en |
| 2 | INVEST-ITHOME99 | ["z920"] | en |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | jp |
| 2 | INVEST-ITHOME99 | ["z920"] | jp |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | ch |
| 2 | INVEST-ITHOME99 | ["z920"] | ch |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | en |
| 2 | INVEST-ITHOME99 | ["z920"] | en |
| 1 | INVEST-ITHOME21 | ["w102","w103","q200","z920"] | jp |
| 2 | INVEST-ITHOME99 | ["z920"] | jp |
+------------+-----------------+-------------------------------+----------+
12 rows in set (0.00 sec)
UNION -> 將兩個(以上)SQL查詢結果合併起來,查詢的各別SQL語句使用欄位要是相同的資料型別及順序。
ps.MYSQL沒有FULL JOIN,可以用UNION來達到類似作用。
差異點:
JOIN: 橫向結合 (合併多個資料表的各欄位)。
UNION: 垂直結合 (合併多個資料表中的紀錄)。
使用: 查詢act_game&act_name的紀錄。
1. UNION 會顯示2表含幾個不同的actives_id。
2. UNION ALL 會列出所有的資料,不論資料是否重複。
語法:
SELECT column_name... FROM table_name1
UNION
SELECT column_name... FROM table_name2;
mysql> SELECT actives_id FROM act_game
UNION
SELECT actives_id FROM act_name;
+------------+
| actives_id |
+------------+
| 1 |
| 2 |
| 3 |
+------------+
3 rows in set (0.00 sec)
mysql> SELECT actives_id FROM act_game UNION ALL SELECT actives_id FROM act_name;
+------------+
| actives_id |
+------------+
| 1 |
| 2 |
| 1 |
| 1 |
| 1 |
| 3 |
| 3 |
| 3 |
+------------+
8 rows in set (0.00 sec)
介紹完以上資料表連結的方式後,最後來個3表之間的連結查詢當作結尾
使用: 連結 act & act_game & act_name 取得活動ID=1的完整內容。
3張表的全部欄位資訊:
SELECT act.* , act_game.* , act_name.*
FROM `act`
left join act_game on act.actives_id = act_game.actives_id
left join act_name on act.actives_id = act_name.actives_id
WHERE (act.actives_id = 1);
過濾其他不需要的欄位後:
SELECT act.actives_id, act.status, act.start_time, act.end_time, act.platform_id, act_game.game_name, act_game.support_rules, act_game.type, act_name.actives_name, act_name.language FROM `act` left join act_game on act.actives_id = act_game.actives_id left join act_name on act.actives_id = act_name.actives_id WHERE (act.actives_id = 1);